﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace HMS.UIRepository.WPFByDK.ViewModel
{
    public class ReportingService
    {
        public IEnumerable<object> GetPubInfoService()
        {
            using(var EF = new LinqCntxt.PubsDBEntity())
            {
                var bookSaled = EF.titles.Join( EF.publishers, pub=>pub.pub_id, title=>title.pub_id,
                    (A,B)=> new {
                        价格 = A.price,出版日期 = A.pubdate,
                        出版社编号 = B.pub_id,出版社名 = B.pub_name,
                        机构状态 = B.state,前言 = A.notes,
                    });
                return bookSaled.ToList();
            }
        }
        // 获取 南风数据库 出版社销售情况 根据某一个关键字进行查询
        public IEnumerable<object> GetSalesInfoService()
        {
            using (var context = new LinqCntxt.PubsDBEntity())
            {
                /* 分组查询 查询表A中 ID出现次数大于3的数据 - 水晶报表(任意筛选过滤条件) */
                var TopMostSales = 2;
                var Linked3In1Tb = (from sa in context.sales
                                    join st in context.stores on sa.stor_id equals st.stor_id
                                    join ti in context.titles on sa.title_id equals ti.title_id
                                    let topKey = from p in context.sales
                                                 group p by p.stor_id into g
                                                 where g.Count() >= TopMostSales select g.Key
                                    where topKey.Contains(sa.stor_id)
                                    orderby sa.stor_id
                                    select new {
                                        主键ID = sa.stor_id,售出时间 = sa.ord_date,销售代码 = st.zip,
                                        书店地址 = st.stor_address,书店店名 = st.stor_name,
                                        书籍价格 = ti.price,书名 = ti.title_id,
                                        上架类型 = ti.type,出版日期 = ti.pubdate,
                                        前言 = ti.notes,
                                    }).ToList();
                return Linked3In1Tb;
            }
        }
    }
}
